package com.caifu.mapper;

import com.caifu.pojo.ZcOrdering;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单表 Mapper 接口
 * </p>
 *
 * @author Lyf
 * @since 2020-11-09
 */
public interface ZcOrderingMapper extends BaseMapper<ZcOrdering> {
    //&lt;  小于  = 等于
    @Select("<script>"+
            " select id,org_id,month,name,date_format(create_time,'%Y-%m-%d %H:%i:%s') as create_time,date_format(update_time,'%Y-%m-%d %H:%i:%s') as update_time,status from zc_ordering where status &lt;&gt; '9' and org_id in (" +
            " <foreach collection='orgs' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " and cast(month as signed integer) ${symbol} cast(#{month} as signed integer)" +
            " <if test='name != null and name != &quot;&quot;'>"+
            " and name like concat('%',#{name},'%')"+
            " </if >" +
            " <if test='state != null and state != &quot;&quot;'>"+
            " and status = #{state}"+
            " </if >" +
            " order by month desc,create_time desc limit #{start},#{pageSize}"+
            " </script>")
    List<Map<String,String>> getOrderList(@Param("start")int start, @Param("pageSize") int pageSize, @Param("name") String name, @Param("orgs") List<String> orgs,@Param("state") String state, @Param("month") String month, @Param("symbol") String symbol);

    @Select("<script>"+
            " select id from zc_ordering where status &lt;&gt; '9' and org_id in (" +
            " <foreach collection='orgs' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " and cast(month as signed integer) = cast(#{month} as signed integer)" +
            " <if test='name != null and name != &quot;&quot;'>"+
            " and name like concat('%',#{name},'%')"+
            " </if >" +
            " <if test='state != null and state != &quot;&quot;'>"+
            " and status = #{state}"+
            " </if >" +
            " </script>")
    List<String> getOrderList_two(@Param("name") String name, @Param("orgs") List<String> orgs,@Param("state") String state, @Param("month") String month);

    @Select("<script>"+
            " select count(*) as count from zc_ordering where status &lt;&gt; '9' and org_id in (" +
            " <foreach collection='orgs' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " and cast(month as signed integer) ${symbol} cast(#{month} as signed integer)" +
            " <if test='name != null and name != &quot;&quot;'>"+
            " and name like concat('%',#{name},'%')"+
            " </if >" +
            " <if test='state != null and state != &quot;&quot;'>"+
            " and status = #{state}"+
            " </if >" +
            " </script>")
    int getOrderListCount(@Param("name") String name,@Param("orgs") List<String> orgs,@Param("state") String state,@Param("month") String month,@Param("symbol") String symbol);

    @Select("select * from zc_ordering where org_id = #{orgNo} order by month desc limit 0,1")
    ZcOrdering getLately(@Param("orgNo") String orgNo);

    @Select("<script>"+
            " select a.id,a.type,c.org_name,a.month,a.day,b.name,date_format(b.order_time,'%Y-%m-%d %H:%i:%s') as date_time,a.confirm_status as distribution_status,a.count1,a.count2,a.count3,a.confirm_time from (select o.id,ord.type,ord.month,ord.day,count(if(ord.food_type = '1' ,'1',null)) as count1,count(if(ord.food_type = '2' ,'1',null)) as count2,count(if(ord.food_type = '3' ,'1',null)) as count3,ord.confirm_status,date_format(ord.confirm_time,'%Y-%m-%d %H:%i:%s') as confirm_time from zc_ordering_record_details ord left join zc_ordering_records ore on ord.record_id = ore.id left join zc_ordering o on ore.order_id = o.id where o.status = '3' and ore.status = '1' group by o.id,ord.type,ord.month,ord.day,ord.confirm_status,date_format(ord.confirm_time,'%Y-%m-%d %H:%i:%s') order by ord.month desc,ord.day,ord.type) as a left join zc_ordering b on a.id = b.id left join sys_organization c on b.org_id = c.org_id where b.org_id in (" +
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " <if test='startDate != null and startDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &gt;= #{startDate}"+
            " </if >" +
            " <if test='endDate != null and endDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &lt;= #{endDate}"+
            " </if >" +
            " <if test='type != null and type != &quot;&quot;'>"+
            " and a.type = #{type}"+
            " </if >" +
            " <if test='orderStatus != null and orderStatus != &quot;&quot;'>"+
            " and a.confirm_status = #{orderStatus}"+
            " </if >" +
            " order by a.month,a.day,a.type limit #{start},#{pageSize}"+
            " </script>")
    List<Map<String,String>> getPlatformData(@Param("start") int start,@Param("pageSize") int pageSize,@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("type") String type,@Param("orderStatus") String orderStatus,@Param("orgList") List<String> orgList);

    @Select("<script>"+
            " select count(*) as count from (select o.id,ord.type,ord.month,ord.day,count(if(ord.food_type = '1' ,'1',null)) as count1,count(if(ord.food_type = '2' ,'1',null)) as count2,count(if(ord.food_type = '3' ,'1',null)) as count3,ord.confirm_status from zc_ordering_record_details ord left join zc_ordering_records ore on ord.record_id = ore.id left join zc_ordering o on ore.order_id = o.id where o.status = '3' and ore.status = '1' group by o.id,ord.type,ord.month,ord.day,ord.confirm_status order by ord.month desc,ord.day,ord.type) as a left join zc_ordering b on a.id = b.id left join sys_organization c on b.org_id = c.org_id where b.org_id in (" +
            " <foreach collection='orgList' index='index' item='item' open='' separator=' , ' close=''>#{item}</foreach>" +
            " )" +
            " <if test='startDate != null and startDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &gt;= #{startDate}"+
            " </if >" +
            " <if test='endDate != null and endDate != &quot;&quot;'>"+
            " and concat(a.month,a.day) &lt;= #{endDate}"+
            " </if >" +
            " <if test='type != null and type != &quot;&quot;'>"+
            " and a.type = #{type}"+
            " </if >" +
            " <if test='orderStatus != null and orderStatus != &quot;&quot;'>"+
            " and a.confirm_status = #{orderStatus}"+
            " </if >" +
            " </script>")
    int getPlatformDataCount(@Param("startDate") String startDate,@Param("endDate") String endDate,@Param("type") String type,@Param("orderStatus") String orderStatus,@Param("orgList") List<String> orgList);

    @Select("select d.org_name,b.food_type,count(a.id) as count,a.month,a.day from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id left join sys_organization d on c.org_id = d.org_id where c.id = #{id} and a.type = #{type} and a.month = #{month} and a.day = #{day} group by d.org_name,b.food_type,a.month,a.day")
    Map<String,String> getDetailsInfo(@Param("id") String id,@Param("month") String month,@Param("day") String day,@Param("type") String type);

    @Select("select count(a.id) as count,b.address from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id where c.id = #{id} and a.type = #{type} and a.month = #{month} and a.day = #{day} and a.food_type in ('1','2') group by b.address")
    Map<String,String> getDetailsFixedPoint(@Param("id") String id,@Param("month") String month,@Param("day") String day,@Param("type") String type);

    @Select("select d.user_name as zd_user_name,d.mobile,e.user_name as fd_user_name,b.address from zc_ordering_record_details a left join zc_ordering_records b on a.record_id = b.id left join zc_ordering c on b.order_id = c.id left join user_info d on a.user_no = d.user_no left join zc_no_archives_user e on a.user_no = e.user_no where c.id = #{id} and a.type = #{type} and a.month = #{month} and a.day = #{day} and a.food_type = '3' group by b.address order by b.user_type,d.user_name,d.user_no,e.user_name,e.user_no")
    List<Map<String,String>> getDetailsToDoor(@Param("id") String id,@Param("month") String month,@Param("day") String day,@Param("type") String type);

    @Select("select a.org_id,b.org_name from sys_user_data_auth a left join sys_organization b on a.org_id = b.org_id where a.user_no = #{userNo} and b.type = '3' order by org_id")
    List<Map<String,String>> getUserOrg(@Param("userNo") int uerNo);

    @Select("select org_name from sys_organization where org_id = #{orgId}")
    String getOrgNameById(@Param("orgId") String orgId);
}
